SSRS Tutorial

Welcome to the SSRS Tutorial. The objective of these tutorials is to gain understanding of SSRS Tool. We will cover topics such as components of ssrs, architecture of ssrs, installation, advantages, etc

In addition to these free SSRS Tutorial, we will also cover common Interview Questions, Issues of SSRS.

About SSRS

SQL Server Reporting Services (SSRS) is a server-based reporting platform that allows us to create and manage a wide variety of different types of report, and deliver them in a range of formats. We can create basic reports containing tables and graphs, or more complex data visualizations, using charts, maps and sparklines. Our reports can draw their data from SQL Server databases, but also from other relational database such as Oracle, and other types of multi-dimensional or XML-based data sources such as SQL Server Analysis Services, Teradata, or Parallel Data Warehouse.

If you want to enrich your career and become a professional in SSRS, then visit Tekslate - a global online training platform: "SSRS Training" This course will help you to achieve excellence in this domain.

Index

• SSRS Report Server
• SSRS Installation
• SSRS Configuration
• Reporting in SSRS

1.What is reporting services in SQL Server?

Ans: SQL Server Reporting Services (SSRS) is a server-based report generating software system from Microsoft. It is part of suite of Microsoft SQL Server services, including SSAS (SQL Server Analysis Services) and SSIS (SQL Server Integration Services). SQL Server Reporting Services (SSRS) is a server-based reporting platform that allows us to create and manage a wide variety of different types of report, and deliver them in a range of formats. We can create basic reports containing tables and graphs, or more complex data visualizations, using charts, maps and sparklines.

2.What is SSRS used for?

Ans: SQL Server Reporting Services (SSRS) is a server-based report generating software system from Microsoft. It is Administered via a Web interface, it can be used to prepare and deliver a variety of interactive and printed reports. SSRS competes with Crystal Reports and other business intelligence tools.

3.What is a report builder in SSRS?

Ans: Report Builder is a report authoring environment for business users who prefer to work in the Microsoft Office environment. It is a strong tool provided in SQL Server Reporting Services where you can just go ahead and make reports of your own choice, with your desired columns and calculations. Once built, you can go ahead and save it on a Report Server to access anytime in the future, too.

4.What is SQL Server Integration Services?

Ans: SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Serverdatabases and updates to multidimensional cube data.

Components Of SSRS

SQL Server Reporting Services 2008 (SSRS) is a full-featured application that provides report design, development, testing, and deployment.  In this section we will introduce the main components of the product:

• Databases
• Windows Service
• Report Designer

Architecture Of SQL Server Reporting Services

As we know SSRS is Reporting tool which means should have reach and fast user interface Primary Component. The following diagram shows the architecture of SQL Server Reporting Services.

Tools and Components of SQL Server Reporting Services architecture

This architecture consists mainly of the following types of components and tools.

• Report Builder
• Report Designer
• Report Manager
• Report Server
• Report server database
• Data sources

Report Builder

This component can be used to drag and drop and for easy to use report design functionality. Report Builder is an ad-hoc end-user report publishing tool that is executed on a client computer.

Report Designer

The Report designer tool can be used to develop simple and complex reports. Report Designer is a publishing tool,  that is hosted in Business Intelligence Development Studio (BIDS) or Visual Studio.

Report Manager

This tool provides easy access to web-based reports. The default URL of the Report Manager is http://<server>/reports.

Report Server

It is a server that uses the SQL Server database engine to store metadata.

Report server database

It stores metadata, resources, report definitions, security settings, delivery data and so on.

Data sources

Reporting services retrieves data from data sources like relational and multidimensional data sources.

Processing of SQL Server Reporting Services

This diagram shows the processing of SQL Server Reporting Services.

On a report server, or a different server we choose, we will have access to the Report Manager website. The Report Manager website can be used to deploy and manage reports. End users run reports by the Report Manager and publish their reports if they have permission. An SQL Server Reporting Services is associated with a SQL Server instance. The SQL Server instance has two databases, the first is a report server and the second one is ReportServerTempdb. The end-user sends a HTTP request for a report. The report server finds the metadata of the report and sends a request for data to the data source then after the data is returned by the data source and merged with the report definition into a report. When the report is generated then it is sent to the client.

SSRS Installation

When running the SSRS installation, you will need to install following features:

• Database Engine
• SQL Server Reporting Services – Native (if installing 2008 R2, you’ll be asked on a subsequent screen to select the mode)
• Management Tools – Basic
• Management Tools – Complete
• Business Intelligence Development Tools or SQL Server Data Tools if available with the media you are using, which is dependent on the version

It is easier to install both services at one time, because you can choose to have the installation process configure the SSRS services for you. If SSRS is installed later, or if you select not to configure it at installation, you will have to configure it manually.

Advantages of SQL Server Reporting Services

• Direct and efficient reporting access to information residing in both Oracle and MS SQL Server databases.
• Faster (and therefore cheaper) production of reports on both relational and cube data.
• An easy to deploy centralised reporting infrastructure based on Microsoft Reporting Services.
• Faster delivery of information to the business, providing better decision support.
• Ability for the business to self-serve, edit and interact with information without having to rely on IT or IS resources.
• Simple pricing model tailored for both entry and enterprise level installations, allowing for inexpensive provision of Business Intelligence for the Masses and democratisation of information.
• The beauty is that the entire report and data source definition is stored as a simple XML file. This is the file the reporting engine uses to render reports. The elements and attributes required for defining a report format are fully documented. Further, you can even add your custom elements if you want to enrich available functionality. Most report writers available today never provided this functionality.
• XML based report definition allows you to directly design reports programmatically and render them. This was very difficult to achieve in currently available report writers.
• How do you access reports usually? By instancing the report writer runtime. Here you don’t have to do that. Because all reports are accessible in one of the two ways:
• By specifying the URL identifying the report on the report server or - By calling the Web service.
• The best part of the reporting server is that the entire functionality is exposed as a single Web service! You can create, manage, and view reports using various methods provided by the web service.
• The security is managed in a role-based manner and can be applied to folders as well as reports.
• User can manage their own reporting needs by accessing reports ad-hoc or by subscribing to the reports. Subscription based reports are automatically sent by mail to the users.
• All reports require parameters to be accepted from users. Here once parameters are defined, the UI for these parameters is automatically generated.